
             
/**
 * 
 *
 * Copyright (c) 2012-2013 JLLT Corp. 2012-2013,All Rights Reserved.
 * This software is published under the 金麟龙腾商贸有限公司.
 * License version 1.0, a copy of which has been included with this
 * distribution in the LICENSE.txt file. 
 *
 * @File name:  TNavKeywordDao.java 
 * @Create on:  2012-11-07 21:05:944
 * @Author      :  高宇舟
 *
 * @ChangeList
 * ---------------------------------------------------
 * Date         Editor              ChangeReasons
 *
 */
           
        package com.boho.ecp.base.dao;

            
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;

import cn.boho.framework.po.CQExp;
import cn.boho.framework.po.CQuery;
import cn.boho.framework.po.CQueryFactoryTool;
import cn.boho.framework.po.DAO;

import com.boho.ecp.po.TNavKeywordPO;
            
        /**
 * 【首页类目搜索关键字表:T_NAV_KEYWORD】的Dao操作类
 * 
 */
public class TNavKeywordDao extends DAO {
	/**
	* 根据条件查询【首页类目搜索关键字表:T_NAV_KEYWORD|】信息
	* @param con
	* @param pKeywordId 父编号
	* @param isValid 是否有效
	* @return
	* @throws Exception
	* @date 2012-11-07
	* @author 高宇舟
	* 
	*/
	public static List queryTNavKeywordByPID(Connection con,java.lang.Integer pKeywordId,java.lang.Integer isValid)throws Exception{
		CQExp cqExp = CQExp.instance().select("select T_NAV_KEYWORD.IS_LIGHT,T_NAV_KEYWORD.P_KEYWORD_ID,T_NAV_KEYWORD.REMARK,T_NAV_KEYWORD.KEYWORD_PHOTO,T_NAV_KEYWORD.KEYWORD_ID,T_NAV_KEYWORD.KEYWORD_NAME,T_NAV_KEYWORD.IS_LINK,T_NAV_KEYWORD.IS_HOT from T_NAV_KEYWORD ")
			.where()
			.filed("T_NAV_KEYWORD.P_KEYWORD_ID",CQExp.EQ,pKeywordId)
			.filed("T_NAV_KEYWORD.IS_VALID",CQExp.EQ,isValid)
			;
		System.out.println(cqExp.getSql());
		return cqExp.getDynaBeanMapList("T_NAV_KEYWORD",con);
	}
	
	/**
	* 查询所有【首页类目搜索关键字表:T_NAV_KEYWORD】信息（树模式）
	* @param con
	* @param keywordId
	* @param keywordLevel
	* @return
	* @throws Exception
	* @date 2012-11-07
	* @author 高宇舟
	*
	*/
	public static List<TNavKeywordPO> queryTNavKeywordForTreeMode(Connection con,int keywordId,int keywordLevel) throws Exception {
		List<TNavKeywordPO> list = new LinkedList<TNavKeywordPO>();
		StringBuffer sql = new StringBuffer();
		sql.append("{call findKwChild(")
			.append(keywordId)
			.append(",")
			.append(keywordLevel)
			.append(")}");
		CallableStatement cs = con.prepareCall(sql.toString());
		ResultSet rs = cs.executeQuery();
		while(rs.next()) {
			if (rs.getInt("KEYWORD_ID") != keywordId) {
				TNavKeywordPO po = new TNavKeywordPO();
				po.setKeywordId(rs.getInt("KEYWORD_ID"));
				po.setKeywordName(rs.getString("KEYWORD_NAME"));
				po.setPKeywordId(rs.getInt("P_KEYWORD_ID"));
				po.setIsLight(rs.getInt("IS_LIGHT"));
				po.setIsLink(rs.getInt("IS_LINK"));
				po.setIsHot(rs.getInt("IS_HOT"));
				list.add(po);
			}
		}
		return list;
	}
	
	/**
	* 根据关键字标识查询【首页类目搜索关键字表:T_NAV_KEYWORD|】所有父类信息
	* @param con
	* @param keywordId 关键字标识
	* @return
	* @throws Exception
	* @date 2012-11-07
	* @author 高宇舟
	* 
	*/
	public static List queryTNavKeywordByKeywordID(Connection con,java.lang.Integer keywordId)throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT T2.KEYWORD_ID, T2.KEYWORD_NAME,T2.P_KEYWORD_ID ");
		sql.append("FROM (");  
		sql.append("    SELECT");    
		sql.append("        @r AS _id,");    
		sql.append("        (SELECT @r := T_NAV_KEYWORD.P_KEYWORD_ID FROM T_NAV_KEYWORD WHERE T_NAV_KEYWORD.KEYWORD_ID = _id) AS PID,");    
		sql.append("        @l := @l + 1 AS LVL");    
		sql.append("    FROM");    
		sql.append("        (SELECT @r := ?, @l := 0) VARS,");    
		sql.append("        T_NAV_KEYWORD T");
		sql.append("    WHERE @r <> 0) T1");    
		sql.append("  JOIN T_NAV_KEYWORD T2");   
		sql.append("  ON T1._id = T2.KEYWORD_ID");
		sql.append(" ORDER BY T1.LVL DESC"); 
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
		query.setInt(1, keywordId);
		return query.getDynaBeanMapList("T_NAV_KEYWORD", con);
	}
}
